dbtでデータを用いたビジネス上の指標を管理できる「Metrics」を試してみた

モダンデータスタック(MDS)

#dbt

#dbt Cloud

さがら

2023.01.28

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

さがらです。

dbtでデータを用いたビジネス上の指標を管理できる「Metrics」を試してみたので、その内容をまとめてみます。

2023年10月29日追記

本記事の内容は古くなっており、すでに使えない仕様となっています。

最新のMetricsの定義方法、dbt Semantic LayerをTableauから参照する方法、については下記の記事をご覧ください。

Metricsとは

まず、Metricsとは何をするための機能かといいますと、ビジネスを行う上での「指標」をdbt上で管理するための機能です。

この「指標」ですが、例としては、下記のようなものが挙げられます。

運営するアプリケーションの月間アクティブユーザー数
会社としての月間経常利益

そして、どうしてdbt上でこういった指標をMetricsとして定義することが良いのか、そのメリットとしては下記のような点が挙げられます。

対象のdbt project内でmetricsマクロを用いることで、定義したMetricsを汎用的に参照できる
対象のMetricsがどのdbt Modelと紐づいているか、リネージを確認できる
dbt Cloudの場合、Semantic Layerを介して外部のツールから定義したMetricsを参照できる
- 例えば、BIツールだとModeやLightdash、データカタログだとAtlanやSecodaが対応しています。
- ※2023年1月27日時点、Semantic Layerはパブリックプレビューで提供されている、かつ使用するDWHはSnowflakeのみ対応しているためご注意ください。

組織でデータ分析基盤が活用されていくと、「あれ、売上の定義って、A+Bだっけ、A+Cだっけ」といった疑問から、ある人は「A+Bだった気がする！」、別の人は「A+Cかな～」と言った流れで、売上の定義は本来1つしかないはずなのに誤った定義が組織内に広まってしまうリスクがあります。

こういった指標の定義を統制するために、Metricsの機能が活きてくると思います。

事前準備

検証の前にリモートリポジトリの準備など行いましたので、その事前準備の内容をまとめておきます。

jaffle_shop_metricsリポジトリのfork

今回試した内容は、dbt Labs社公式のリポジトリ「jaffle_shop_metrics」を用いたものです。

事前にこのリポジトリを自分のGitHubアカウントを用いてforkして、dbt Cloudから参照できるようにしておきます。

Snowflakeのトライアルアカウントの準備

今回Snowflakeのトライアルアカウントを用いたのですが、事前に以下のクエリを実行して検証用のデータベースとウェアハウスを作成しておきました。今回は検証のため、dbt CloudではSYSADMINロールを用いて各種クエリを実行していきます。

-- データベースの作成
use role sysadmin;

create database dbt_metrics_test;


-- ウェアハウスの作成
use role sysadmin;

create warehouse dbt_wh 
with
  warehouse_size = 'xsmall' 
  warehouse_type = 'standard'
  auto_suspend = 60 // 300もいらないため、60に変更
  auto_resume = true
  min_cluster_count = 1
  max_cluster_count = 1
  scaling_policy = 'standard';

Snowflakeとforkしたリモートリポジトリを用いるdbt Projectの新規作成

上述のforkしたリモートリポジトリとSnowflakeトライアルアカウントを用いて、dbt Projectを新規作成します。

手順としては、dbt CloudのGUIが変更されていますが下記のブログが参考になると思います。

dbt seed・dbt runの実行

一度正しくdbtからSnowflake上のオブジェクト操作が出来るかの動作確認も込めて、IDEでブランチを切って、dbt seedとdbt runを実行して対象のDWHにスキーマとテーブルが作成されるかを確認してみます。

dbt seedにより、3つテーブルが作られます。

dbt runにより、8個のModelが実行されてテーブルやビューが作られます。このうち、example_develop_metricがエラーとなっていますが、これはMetricsを用いたクエリの書き方のサンプルが書かれているだけのため、気にしなくてOKです。

検証した環境

参考までに、検証時に用いた各プロダクトやdbt packageのバージョンについて記しておきます。

dbt Cloud
- dbt：1.3　※Environmentsから指定
- dbt-labs/metrics：1.3.2
- dbt-labs/dbt_utils：1.0.0
Snowflake
- 7.3.0
- AWS、Asia Pacific(Tokyo)、Enterpriseエディション

jaffle_shop_metricsに含まれるSeed・Modelについて

まず、Metricsを定義する対象のデータについて見ていきます。より具体的には、jaffle_shop_metricsリポジトリにおいてSeedとModelがどのように構築されているかを見ていきます。

今回使用しているjaffle_shop_metricsリポジトリに含まれるSeedとModelを見ると、下図のような依存関係で構成されています。

これまでにjaffle_shopのデータセットを用いたことがある方からすると、見慣れない中間モデルint_order_payments_pivotedとint_customer_order_history_joinedがあると思います。これらのモデルは、jaffle_shopのデータセットにおいて有用なMetricsを定義するために、計算式などを用いて定義されたカラムを追加したものです。

この2つの中間モデルのカラム一覧と、2モデル間のリレーションは下図のような関係となっております。

そして、これらの2つの中間モデルをJOINしたものが、ordersとして定義されています。このordersを用いて、各Metricsがこのリポジトリでは定義されています。

Metricsの書き方

ここでようやっと、本題のMetricsがどう定義されているかを見ていきます！

ここでは、「顧客のステータスなどの粒度ごとに、日・週・月別の平均注文額を表示」することができるaverage_order_amountというMetricsについて、どのように定義されているかを確認していきます。

まず、大前提としてMetricsはyamlファイル上で定義します。yamlファイルの名前は何でもOKですが、yamlファイル内でmetrics:というハッシュを用いてMetricsを定義する必要があります。

実際にMetricsが定義されている/models/marts/average_order_amount.ymlの中身は下記となります。

※注意事項：1点だけ、forkしたリポジトリではdimensionsが「has」から始まっていたのですが、実際には「had」から始まるフィールドがordersで定義しているため、「has」から「had」に私の方で修正しています。

version: 2
metrics:
  - name: average_order_amount
    label: Average Order Amount
    model: ref('orders')
    description: "The average size of a jaffle order"

    calculation_method: average
    expression: amount

    timestamp: order_date
    time_grains: [day, week, month]

    dimensions:
      - had_credit_card_payment
      - had_coupon_payment
      - had_bank_transfer_payment
      - had_gift_card_payment
      - customer_status

このMetricsを参考に、metrics:内で定義できる各プロパティについて、詳細を以下に記します。

name：対象のMetricsを参照する際に用いるユニークな文字列
label：対象のMetricsをよりわかりやすく表記するための任意の文字列
- 「"」で囲むことで日本語も入力可能なので、実際にビジネス上で使用されている指標名を入れるのがおすすめ
model：対象のMetricsが参照するモデル名。基本的にref('モデル名')で入力する
description：対象のMetricsに関する詳細な説明
calculation_method：対象のMetricsの計算方法を指定
- 具体的には、count、sum、averageなど。derivedにすると、複数の定義済Metricsを用いた計算も可能。より詳しくは公式Docをご覧ください
expression：対象のMetricsの計算に用いるフィールド名を指定
timestamp：対象のMetricsを日時で区切って集計する際に用いる、日時データを持つフィールド
time_grains：timestampで指定したフィールドを用いて、日別(week)、月別(month)など、どの粒度で集計してほしいかを指定
- このMetricsを用いたクエリを発行する際、このtime_grainsでリストアップされた日時の粒度を指定する必要があります
dimensions：対象のMetricsをより細かな粒度で見る際に使用してほしいフィールドを指定
- こちらは任意ですが、このMetricsを用いたクエリを発行する際、このdimensionsでリストアップされたフィールドを指定することで、そのフィールドも含めた集計結果を表示してくれるようになります
filters：参照するモデルに含まれる任意のフィールドの値で絞り込んだ上でMetricsを計算できるようになります
- このaverage_order_amountではfiltersを使用していませんが、後述する別のMetricsで使用されています。filtersの定義方法の詳細は公式Docをご覧ください。

その他に定義されているMetricsを見てみる

このjaffle_shop_metricsリポジトリでは、average_order_amountの他に3つのMetricsが定義されています。それぞれどういった指標が定義されているか見ていきます。

expenses

expensesは、jaffle shopのビジネスにおける総コストを計算するMetricsです。

特徴としては、expression: amount / 4とすることで、「amountフィールドの値を４で割った値を、計算に用いる」という仕様となっております。こういった細かな計算もdbtのMetricsは対応可能です。

また、filters:では3つのプロパティが設定されており、これは「statusの値がcompletedのレコードのみに絞り込んで、このMetricsは計算する」ということを意味しています。

以下が、expenses.ymlの中身となります。

version: 2

metrics:
  - name: expenses
    label: Expenses
    model: ref('orders')
    description: "The total expenses of our jaffle business"

    calculation_method: sum
    expression: amount / 4

    timestamp: order_date
    time_grains: [day, week, month, year]

    dimensions:
      - customer_status
      - had_credit_card_payment
      - had_coupon_payment
      - had_bank_transfer_payment
      - had_gift_card_payment

    filters:
      - field: status
        operator: '='
        value: "'completed'"

revenue

revenueは、jaffle shopのビジネスにおける総売上を計算するMetricsです。

基本的には、上述のexpenses.ymlと同じような定義をしています。こちらは総売上なのでamountフィールドをそのまま計算に用いるように定義していますね。

以下が、revenue.ymlの中身となります。

version: 2

metrics:
  - name: revenue
    label: Revenue
    model: ref('orders')
    description: "The total revenue of our jaffle business"

    calculation_method: sum
    expression: amount 

    timestamp: order_date
    time_grains: [day, week, month, year]

    dimensions:
      - customer_status
      - had_credit_card_payment
      - had_coupon_payment
      - had_bank_transfer_payment
      - had_gift_card_payment

    filters:
      - field: status
        operator: '='
        value: "'completed'"

profit

profitは、jaffle shopのビジネスにおける利益を計算するMetricsです。

特徴的なのは、calculation_method: derivedとすることで複数のMetricsを用いた計算であることを定義し、その計算方法はexpression: "{{metric('revenue')}} - {{metric('expenses')}}"と記述しているため、revenueからexpensesを引いた値がこのprofitが返す値ということになります。

以下が、profit.ymlの中身となります。

version: 2

metrics:
  - name: profit
    label: Profit
    description: "The total money we get to take home from our jaffle business"

    calculation_method: derived
    expression: "{{metric('revenue')}} - {{metric('expenses')}}" 

    timestamp: order_date
    time_grains: [hour, day, week, month, year]

    dimensions:
      - customer_status
      - had_coupon_payment
      - had_bank_transfer_payment
      - had_gift_card_payment

Metricsまで含めたリネージ

このjaffle_shop_metricsリポジトリでは4つのMetricsが定義されていることがわかりましたが、実はdbtはMetricsまで含めたリネージも表示してくれます。

下図がMetricsまで含めたリネージです。特にexpensesとrevenueを用いたprofitは、Metricsから派生していることがわかりますね。

Metricsの活用方法

ここまで、実際にdbtのMetricsを定義する方法について述べてきましたが、このMetricsをどう使うかについては触れていませんでした。

そこで、Metrics定義後の活用方法について、述べておきます。

対象のdbt projectにおいてMetricsを参照したクエリを定義しModelとする

定義したMetricsに対しては、dbt Labs社が提供している「metrics」というパッケージに含まれるマクロを用いることで、SELECT文としてクエリを実行することが出来ます。SELECT文のため、通常のモデルと同じく、接続先のDWHにテーブルやビューを生成することができます。

jaffle_shop_metricsリポジトリ内のaverage_order_amountに対するクエリの書き方は下記のようになります。

このクエリでは、metrics.calculateというマクロを用いています。使い方は下記のようなイメージです。

metric：使用するMetricsのnameを指定
grain：対象のMetricsで定義されているtime_grainsのうち、使用したい粒度を指定
dimensions：対象のMetricsで定義されているdimensionsのうち、より細かい粒度で見たい(GROUP BYに追加したい)フィールドを指定

select * 
from {{ metrics.calculate(
    metric('average_order_amount'),
    grain='week',
    dimensions=['customer_status'],
) }}

実際にこのクエリをdbt上で実行してみると、下図のような結果が返ってきます。grain='week'としていたのでDATE_WEEK列は週ごとの月曜日の日付が記載されており、dimensions=['customer_status']としていたのでcustomer_status列も結果に加わっています。

もう一つ例として、下記のクエリを実行してみます。

select * 
from {{ metrics.calculate(
    metric('average_order_amount'),
    grain='month',
    dimensions=['customer_status', 'had_credit_card_payment'],
) }}

実際にこのクエリをdbt上で実行してみると、下図のような結果が返ってきます。 grain='month'としていたのでDATE_MONTH列は各月の初日の日付が記載されており、dimensions=['customer_status', 'had_credit_card_payment']としていたのでcustomer_status列とhad_credit_card_payment列が結果に加わっています。

「あれ、これ使いづらくない？？」と思ってしまった方へ

上述の使い方を見て、「え、time_grainsやdimensionsで色んな条件で指標を計算できるようにMetrics定義しているけど、１つ１つクエリ定義してテーブルやビューを生成しないといけないの？？」と思ってしまった方、いるのではないでしょうか？大丈夫です、私も感じましたｗ

しかし、Metricsの真髄はSemantic Layerを介してこそだと私は感じています。

Semantic Layerを介することで、Modeの例のようにmetrics.calculateを用いたSELECT文をMode上で記述できるようになったり、Secodaの例のように定義したMetricsの情報を自動でデータカタログ上のDictionary機能に連携したり、ということが可能になります。

こういったことが出来るのであれば、dbt上でMetricsを定義する意義も出てくるのではないでしょうか？

Semantic LayerはCoalesce 2022でも目玉機能として取り上げられていたため、今後dbt Labs社も更に力をかけてアップデートをしてくる分野だと思います。dbtの近年の広まり方を見ると、２~３年後にはdbtでMetricsを定義するのがスタンダードになる可能性もあると、私は考えています。

（これは余談ですが、このSemantic Layerを用いて外部からMetricsを参照することについて、一つ試そうと考えているネタがあるので、検証が上手く行けば近日中にブログ化予定です！）